<?
class Twitwords {
	
	/**
	 * Select 'twits' having the selected annotation in the text field, store
	 * users if not already present and relative tags in DB
	 */
	public function parseTwitterData($handler) {
		if(time()-$handler->getLastTimestamp()<$handler->getTimeOut()) {
			/* WAITING TIME NOT EXPIRED YET */
			return -1;
		} elseif(time()-$handler->getLastExceedingRequest()<$handler->getTwitterRateLimit()) {
			/* TWITTER RATE LIMIT EXCEEDED */
			return -2;
		} else {
			/* ALL OK */
			$api = new Twitter($handler->getTwitterUser(), $handler->getTwitterPassword());
			$tags_added = 0;
			$page = 1;
			do {
				$xml = simplexml_load_string($api->getReplies("xml", $page, (int)$handler->getLastStatus()));
				$statuses = $xml->status;
				/* DEBUG */
				//echo $api->lastAPICall();
				//echo $api->lastStatusCode();
				//echo "Loop: ".$xml;
				if(count($xml->error)>0) {
					//echo "A ";
					$handler->setLastExceedingRequest(time());
					$more_status = false;
				} elseif(count($statuses)==0 || (count($statuses)>0 && $page>1 && (count($statuses)<20 ||
					(int)$handler->getLastStatus()>=(int)$statuses[count($statuses)-1]->id))) {
					//echo "B ";
					$handler->setLastParsing(time());
					$more_status = false;
				} else {
					//echo "C ";
					if($page==1 && (int)$statuses[0]->id>(int)$handler->getLastStatus()) {
						//echo "D ";
						$handler->setLastParsing(time(), $statuses[0]->id);
					}
					$tags_added += $this->persistXML($statuses, $handler);
					$page++;
					$more_status = (count($statuses)<20)? false : true;
				}
			} while($more_status);
			return $tags_added;
		}

	}

	/**
	 * Flip through the array of status fetched from the Twitter XML file
	 * and save to DB all new users and tags
	 */
	public function persistXML($statuses, $handler) {
		$tags_added = 0;
		foreach($statuses as $status) {
			if($this->userNotInDB($status->user->id)) {
				$this->persistUser(new User(
					$status->user->id,
					$status->user->screen_name,
					$status->user->name,
					$status->user->profile_image_url,
					$status->user->location,
					$status->user->url
				));
			}
			/*******************************************
			 * check message pattern and store tag(s)  *
			 * OK => annotation tag[, tag]*            *
			 * tag => [a-zA-Z0-9]([a-zA-Z0-9]| |_)+    *
			 *******************************************/
			$text = str_replace($handler->getAnnotation(), "", $status->text);
			$tags = explode($handler->getTagSeparator(), $text);
			foreach($tags as $tag) {
				$max = (int)$handler->getMaxPriority();
				//echo $tag." => ";
				if(preg_match("/\[[1-9][0-9]*\]/i", $tag)) {
					$priority = (int)substr($tag, strpos($tag, "[")+1, strpos($tag, "]")-1);
					//echo $priority." ";
					$priority = ($priority>$max)? $priority%$max+1 : $priority;
					//echo $priority." <br />";
					$tag = preg_replace("/\[[1-9][0-9]*\]/i", "", $tag);
				} else {
					$priority = (int)$handler->getDefaultPriority();
				}
				$clean = str_replace("'", "\'", trim($tag));
				//echo $clean." => ".$priority;
				if($clean!="") {
					$time = $this->xmlToDate($status->created_at, $gmt);
					if($this->persistTag($status->id, $status->user->id, $clean, $time, $priority)) {
						$tags_added++;
					}
				}
			}
		}
		return $tags_added;
	}
	
	/**
	 * Convert UNIX timestamp to Twitter date
	 */
	public function dateToXML($time, $gmt = NULL) {
		//2008-04-20 15:07:29 => Sun, 20 Apr 2008 15:07:29 GMT
		$delta = ($gmt!=NULL)? (int)$gmt : 2;
		return sprintf("%s GMT", date("D, d M Y H:i:s", (int)$time-$delta*3600));
	}
	
	/**
	 * Convert Twitter date to UNIX timestamp
	 */
	public function xmlToDate($str, $gmt = NULL) {
		//Sun Apr 20 15:07:29 +0000 2008 => 2008-04-20 15:07:29
		$real = date("Y-m-d H:i:s", strtotime($str));
		return $real;
	}
	
	/**
	 * Retrieve the number of stored users
	 */
	public function getUsersNumber() {
		$query = "SELECT COUNT(*) AS total FROM USERS";
		$result = mysql_query($query);
		if(mysql_num_rows($result)!=1) {
			return -1;
		} else {
			$row = mysql_fetch_array($result, MYSQL_ASSOC);
			return $row['total'];
		}
	}
	
	/**
	 * Retrieve the number of stored tags
	 */
	public function getTagsNumber() {
		$query = "SELECT COUNT(*) AS total FROM TAGS";
		$result = mysql_query($query);
		if(mysql_num_rows($result)!=1) {
			return -1;
		} else {
			$row = mysql_fetch_array($result, MYSQL_ASSOC);
			return $row['total'];
		}
	}
	
	/**
	 * Update the displayed profile of the spcific Twitter user
	 */
	public function updateProfile($userid, $handler) {
		//$userid = $this->loadUserByName($username)->getUserId();
		$api = new Twitter($handler->getTwitterUser(), $handler->getTwitterPassword());
		$user = simplexml_load_string($api->showUser("xml", $userid));
		$query = sprintf("UPDATE USERS SET username='%s', name='%s', img='%s', location='%s', url='%s' ".
			"WHERE userid='%s'", $user->screen_name, $user->name, $user->profile_image_url,
			$user->location, $user->url, $userid);
		$result = mysql_query($query);
		//echo $xml->asXML();
		return new User(
			$userid,
			$user->screen_name,
			$user->name,
			$user->profile_image_url,
			$user->location,
			$user->url
		);
	}
	
	/**
	 * Follow all new user following twitwords
	 */
	public function addFriends($api) {
		$followers = simplexml_load_string($api->getFollowers("xml"));
		foreach($followers->user as $user) {
			if($this->userNotInDB($user->id)) {
				$api->createFriendship("xml", $user->id);
				//echo sprintf("<p>Added friendship with %s</p>", $user->screen_name);
			}
		}
	}
	
	/**
	 * Return the user stored in DB having the specified letter as initial
	 */
	public function loadUserByInitial($letter, $offset = -1, $quantity = -1) {
		$users = array();
		$limit = ($offset>=0 && $quantity>=0)? "LIMIT $offset, $quantity" : "";
		$query = "SELECT * FROM USERS WHERE username LIKE '$letter%' ORDER BY username $limit";
		$result = mysql_query($query);
		while($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
			$users[] = new User(
				$row['userid'],
				$row['username'],
				$row['name'],
				$row['img'],
				$row['location'],
				$row['url']
			);
		}
		return $users;
	}
	
	/**
	 * Return the user stored in DB corresponding to the unique identifier userid
	 */
	public function loadUserById($userid) {
		$query = sprintf("SELECT * FROM USERS WHERE userid='%s'", $userid);
		$result = mysql_query($query);
		if(mysql_num_rows($result)!=1) {
			return NULL;
		} else {
			$row = mysql_fetch_array($result, MYSQL_ASSOC);
			$user = new User(
				$row['userid'],
				$row['username'],
				$row['name'],
				$row['img'],
				$row['location'],
				$row['url']
			);
			return $user;
		}
	}
	
	/**
	 * Return the user stored in DB corresponding to the unique identifier username
	 */
	public function loadUserByName($username) {
		$query = sprintf("SELECT * FROM USERS WHERE username='%s'", $username);
		$result = mysql_query($query);
		if(mysql_num_rows($result)!=1) {
			return NULL;
		} else {
			$row = mysql_fetch_array($result, MYSQL_ASSOC);
			$user = new User(
				$row['userid'],
				$row['username'],
				$row['name'],
				$row['img'],
				$row['location'],
				$row['url']
			);
			return $user;
		}
	}
	
	/**
	 * Return the number of tags of the specified user
	 */
	public function countTagOfUser($userid) {
		$query = sprintf("SELECT COUNT(*) AS total FROM TAGS WHERE userid='%s'", $userid);
		//echo $query;
		$result = mysql_query($query);
		if(mysql_num_rows($result)!=1) {
			return -1;
		} else {
			$row = mysql_fetch_array($result, MYSQL_ASSOC);
			return $row['total'];
		}
	}
	
	/**
	 * Return all users ordered by name
	 */
	public function loadAllUsers($offset = -1, $quantity = -1) {
		$users = array();
		$limit = ($offset>=0 && $quantity>=0)? "LIMIT $offset, $quantity" : "";
		$query = sprintf("SELECT * FROM USERS ORDER BY username %s", $limit);
		$result = mysql_query($query);
		while($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
			$users[] = new User(
				$row['userid'],
				$row['username'],
				$row['name'],
				$row['img'],
				$row['location'],
				$row['url']
			);
		}
		return $users;
	}
	
	/**
	 * Store the object user in DB
	 */
	public function persistUser($user) {
		$query = sprintf("INSERT INTO USERS(userid, username, name, img, location, url) ".
			"VALUES('%s', '%s', '%s', '%s', '%s', '%s')",
			$user->getUserid(),
			$user->getUsername(),
			$user->getName(),
			$user->getImage(),
			$user->getLocation(),
			$user->getURL()
		);
		return mysql_query($query);
	}
	
	/**
	 * Check whether an user with a specific userid is already stored in DB or not
	 */
	public function userNotInDB($userid) {
		$query = sprintf("SELECT * FROM USERS WHERE userid='%s'", $userid);
		$result = mysql_query($query);
		return mysql_num_rows($result)==0;
	}
	
	/**
	 * Search for a specific user in DB with FULLTEXT
	 */
	public function fullTextSearchUser($username, $offset = -1, $quantity = -1) {
		$users = array();
		$limit = ($offset>=0 && $quantity!=NULL)? "LIMIT $offset, $quantity" : "";
		$query = sprintf("SELECT * FROM USERS ".
			"WHERE MATCH(username) AGAINST('%s') %s", $username, $limit);
		$result = mysql_query($query);
		while($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
			$users[] = new User(
				$row['userid'],
				$row['username'],
				$row['name'],
				$row['img'],
				$row['location'],
				$row['url']
			);
		}
		return $users;
	}
	
	/**
	 * Search for a specific user in DB with LIKE
	 */
	public function searchUser($username, $offset = -1, $quantity = -1) {
		$users = array();
		$limit = ($offset>=0 && $quantity>=0)? "LIMIT $offset, $quantity" : "";
		$query = "SELECT * FROM USERS WHERE username LIKE '%$username%' ".
			"ORDER BY username ASC $limit";
		//echo $query;
		$result = mysql_query($query);
		while($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
			$users[] = new User(
				$row['userid'],
				$row['username'],
				$row['name'],
				$row['img'],
				$row['location'],
				$row['url']
			);
		}
		return $users;
	}
	
	/**
	 * Return an object containing all tags belonging to a specific user
	 */
	public function loadTags($user, $offset = -1, $quantity = -1) {
		$tags = array();
		$userid = $user->getUserid();
		$limit = ($offset>=0 && $quantity>=0)? "LIMIT $offset, $quantity" : "";
		$query = sprintf("SELECT * FROM TAGS WHERE userid='%s' ".
			"ORDER BY date DESC, tag ASC %s", $userid, $limit);
		//echo $query;
		$result = mysql_query($query);
		while($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
			//echo $row['priority'];
			$tags[] = new Tag($row['status'], $row['tag'], $row['date'], $row['priority']);
		}
		return $tags;
	}
	
	/**
	 * Return all tags ordered by date
	 */
	public function loadAllTags($offset = -1, $quantity = -1) {
		$tags = array();
		$limit = ($offset>=0 && $quantity>=0)? "LIMIT $offset, $quantity" : "";
		$query = sprintf("SELECT * FROM TAGS T, USERS U ".
			"WHERE T.userid=U.userid ORDER BY date DESC %s", $limit);
		$result = mysql_query($query);
		while($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
			$tags[] = new Tag(
				$row['status'],
				$row['tag'],
				$row['date'],
				$row['priority'],
				new User(
					$row['userid'],
					$row['username'],
					$row['name'],
					$row['img'],
					$row['location'],
					$row['url']
				)
			);
		}
		return $tags;
	}
	
	/**
	 * Store a tag for a specific user in DB
	 */
	public function persistTag($status, $userid, $tag, $date = NULL, $priority = 2) {
		$ins = "status, userid, tag, priority";
		$val = "'$status', '$userid', '$tag', '$priority'";
		if($date!=NULL) {
			$ins .= ", date";
			$val .= ", '$date'";
		}
		$query = sprintf("INSERT INTO TAGS(%s) VALUES(%s)", $ins, $val);
		//echo $query;
		return mysql_query($query);
	}
	
	/**
	 * Check whether a tag for a specific user is already stored in DB or not
	 */
	public function tagNotInDB($status) {
		$query = sprintf("SELECT * FROM TAGS WHERE status='%s'", $status);
		$result = mysql_query($query);
		//if(!$result) echo mysql_error();
		return mysql_num_rows($result)==0;
	}
	
	/**
	 * Search for a tag in DB with FULLTEXT
	 */
	public function fullTextSearchTag($tag, $offset = -1, $quantity = -1) {
		$tags = array();
		$limit = ($offset>=0 && $quantity>=0)? "LIMIT $offset, $quantity" : "";
		$query = sprintf("SELECT * FROM TAGS T, USERS U ".
			"WHERE U.userid=T.userid AND MATCH(tag) AGAINST('%s') %s", $tag, $limit);
		$result = mysql_query($query);
		while($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
			$tags[] = new Tag(
				$row['status'],
				$row['tag'],
				$row['date'],
				$row['priority'],
				new User(
					$row['userid'],
					$row['username'],
					$row['name'],
					$row['img'],
					$row['location'],
					$row['url']
				)
			);
		}
		return $tags;
	}
	
	/**
	 * Search for a tag in DB with LIKE
	 */
	public function searchTag($tag, $offset = -1, $quantity = -1) {
		$tags = array();
		$limit = ($offset>=0 && $quantity>=0)? "LIMIT $offset, $quantity" : "";
		$query = "SELECT * FROM TAGS T, USERS U ".
			"WHERE U.userid=T.userid AND tag LIKE '%$tag%' ORDER BY date DESC $limit";
		$result = mysql_query($query);
		while($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
			$tags[] = new Tag(
				$row['status'],
				$row['tag'],
				$row['date'],
				$row['priority'],
				new User(
					$row['userid'],
					$row['username'],
					$row['name'],
					$row['img'],
					$row['location'],
					$row['url']
				)
			);
		}
		return $tags;
	}
	
	/**
	 * Search for tags belonging to a specific user in DB with FULLTEXT
	 */
	public function fullTextSearchTagOfUser($tag, $username, $offset = -1, $quantity = -1) {
		$tags = array();
		$limit = ($offset>=0 && $quantity>=0)? "LIMIT $offset, $quantity" : "";
		$query = sprintf("SELECT tag, date FROM TAGS T, USERS U ".
			"WHERE U.userid=T.userid AND MATCH(tag) AGAINST('%s') ".
			"AND MATCH(username) AGAINST('%s') %s", $tag, $username, $limit);
		$result = mysql_query($query);
		while($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
			$tags[] = array(
				new Tag($row['status'], $row['tag'], $row['date'], $row['priority'])
			);
		}
		return $tags;
	}
	
	/**
	 * Search for tags belonging to a specific user in DB with LIKE
	 */
	public function searchTagOfUser($tag, $username, $offset = -1, $quantity = -1) {
		$tags = array();
		$limit = ($offset>=0 && $quantity>=0)? "LIMIT $offset, $quantity" : "";
		$query = "SELECT tag, date FROM TAGS T, USERS U WHERE U.userid=T.userid ".
			"AND tag LIKE '%$tag%' AND username LIKE '%$username%' $limit";
		$result = mysql_query($query);
		while($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
			$tags[] = array(
				new Tag($row['status'], $row['tag'], $row['date'], $row['priority'])
			);
		}
		return $tags;
	}
	
	/**
	 * Retrieve the average tag priority of the specified user, if user is
	 * unspecified the function returns the average tag priority of all tags
	 */
	public function avgTagPriority($userid = NULL) {
		$where = ($userid!=NULL)? "WHERE userid='$userid'" : "";
		$query = sprintf("SELECT COUNT(*) AS total, AVG(priority) AS avg FROM TAGS %s", $where);
		$result = mysql_query($query);
		if(mysql_num_rows($result)!=1) {
			return -1;
		} else {
			$row = mysql_fetch_array($result, MYSQL_ASSOC);
			return array(
				"avg" => $row['avg'],
				"total" => $row['total']
			);
		}
	}
	
}
?>